using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SouthernLand.Common.BOL.Content;

namespace SouthernLand.Common.DAL.Content
{
    public class ContentsDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new Contents instance filled with the DataReader's current record data 
        /// </summary>
        private Contents GetContentsFromReader(IDataReader dr)
        {
            var obj = new Contents();
            obj.ContentID = (dr["ContentID"] is DBNull) ? Int32.MinValue : (Int32) dr["ContentID"];
            obj.Title = (dr["Title"] is DBNull) ? String.Empty : (String) dr["Title"];
            obj.Title_en = (dr["Title_en"] is DBNull) ? String.Empty : (String) dr["Title_en"];
            obj.Content = (dr["Content"] is DBNull) ? String.Empty : (String) dr["Content"];
            obj.Content_en = (dr["Content_en"] is DBNull) ? String.Empty : (String) dr["Content_en"];
            obj.DisplayOrder = (dr["DisplayOrder"] is DBNull) ? Int32.MinValue : (Int32) dr["DisplayOrder"];
            obj.IsActive = (dr["IsActive"] is DBNull) ? false : (Boolean) dr["IsActive"];
            return obj;
        }

        #endregion

        /// <summary>
        /// Returns a collection of Contents with the data read from the input DataReader
        /// </summary>
        private List<Contents> GetContentsCollectionFromReader(IDataReader dr)
        {
            var obj = new List<Contents>();
            while (dr.Read())
                obj.Add(GetContentsFromReader(dr));
            return obj;
        }

        public int Insert(Contents obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Contents_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ContentID", SqlDbType.Int).Value = obj.ContentID;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@Title_en", SqlDbType.NVarChar).Value = obj.Title_en;
                myCommand.Parameters.Add("@Content", SqlDbType.NText).Value = obj.Content;
                myCommand.Parameters.Add("@Content_en", SqlDbType.NText).Value = obj.Content_en;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

                return myCommand.ExecuteNonQuery();
            }
        }

        public bool Update(Contents obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Contents_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ContentID", SqlDbType.Int).Value = obj.ContentID;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@Title_en", SqlDbType.NVarChar).Value = obj.Title_en;
                myCommand.Parameters.Add("@Content", SqlDbType.NText).Value = obj.Content;
                myCommand.Parameters.Add("@Content_en", SqlDbType.NText).Value = obj.Content_en;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(Int32 ContentID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Contents_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ContentID", SqlDbType.Int).Value = ContentID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the w4u_Contents with the specified ID
        /// </summary>
        public Contents GetContentsByID(Int32 ContentID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Contents_GetContentsByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ContentID", SqlDbType.Int).Value = ContentID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetContentsFromReader(dr) : null);
            }
        }

        //Get All Contents
        public List<Contents> GetAllContents(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Contents_GetAllContents", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetContentsCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  Contents
        public int GetCountContents()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Contents_GetCountContents", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int) myCommand.ExecuteScalar();
            }
        }
    }
}